<?php

class Database_Adapter_Pgsql extends Database_Adapter
{

    /**
     * PDO type.
     *
     * @var string
     */
    protected $_pdoType = 'pgsql';

    /**
     * Keys are UPPERCASE SQL datatypes or the constants
     * Database::INT_TYPE, Database::BIGINT_TYPE, or Database::FLOAT_TYPE.
     *
     * Values are:
     * 0 = 32-bit integer
     * 1 = 64-bit integer
     * 2 = float or decimal
     *
     * @var array Associative array of datatypes to values 0, 1, or 2.
     */
    protected $_numericDataTypes = array(
        Database::INT_TYPE    => Database::INT_TYPE,
        Database::BIGINT_TYPE => Database::BIGINT_TYPE,
        Database::FLOAT_TYPE  => Database::FLOAT_TYPE,
        'INTEGER'            => Database::INT_TYPE,
        'SERIAL'             => Database::INT_TYPE,
        'SMALLINT'           => Database::INT_TYPE,
        'BIGINT'             => Database::BIGINT_TYPE,
        'BIGSERIAL'          => Database::BIGINT_TYPE,
        'DECIMAL'            => Database::FLOAT_TYPE,
        'DOUBLE PRECISION'   => Database::FLOAT_TYPE,
        'NUMERIC'            => Database::FLOAT_TYPE,
        'REAL'               => Database::FLOAT_TYPE
    );
	
	/**
	 * Default class name for a DB statement.
	 *
	 * @var string
	 */
	protected $_defaultStmtClass = 'Database_Statement_Pdo';

	/**
	 * Creates a PDO DSN for the adapter from $this->_config settings.
	 *
	 * @return string
	 */
	protected function _dsn()
	{
		// baseline of DSN parts
		$dsn = $this -> _config;

		// don't pass the username, password, charset, persistent and driver_options in the DSN
		unset($dsn['username']);
		unset($dsn['password']);
		unset($dsn['options']);
		unset($dsn['charset']);
		unset($dsn['persistent']);
		unset($dsn['driver_options']);

		// use all remaining parts in the DSN
		foreach ($dsn as $key => $val)
		{
			$dsn[$key] = "$key=$val";
		}

		return $this -> _pdoType . ':' . implode(';', $dsn);
	}

	/**
	 * Creates a PDO object and connects to the database.
	 *
	 * @return void
	 * @throws Database_Exception
	 */
	protected function _connect()
	{
		// if we already have a PDO object, no need to re-connect.
		if ($this -> _connection)
		{
			return;
		}

		// get the dsn first, because some adapters alter the $_pdoType
		$dsn = $this -> _dsn();

		// check the PDO driver is available
		if (!in_array($this -> _pdoType, PDO::getAvailableDrivers()))
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception('The ' . $this -> _pdoType . ' driver is not currently installed');
		}

		// create PDO connection

		if (PROFILLING)
		{
			$token = Profiler::start('sql', 'connect '.$dsn);
		}

		// add the persistence flag if we find it in our config array
		if (isset($this -> _config['persistent']) && ($this -> _config['persistent'] == true))
		{
			$this -> _config['driver_options'][PDO::ATTR_PERSISTENT] = true;
		}

		try
		{
			$this -> _connection = new PDO($dsn, $this -> _config['username'], $this -> _config['password'], $this -> _config['driver_options']);

			if (PROFILLING)
			{
				Profiler::stop($token);
			}

			// set the PDO connection to perform case-folding on array keys, or not
			$this -> _connection -> setAttribute(PDO::ATTR_CASE, $this -> _caseFolding);

			// always use exceptions.
			$this -> _connection -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		}
		catch (PDOException $e)
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception($e -> getMessage(), $e -> getCode(), $e);
		}
		
		if (!empty($this->_config['charset'])) {
            $sql = "SET NAMES '" . $this->_config['charset'] . "'";
            $this->_connection->exec($sql);
        }

	}

	/**
	 * Test if a connection is active
	 *
	 * @return boolean
	 */
	public function isConnected()
	{
		return ((bool)($this -> _connection instanceof PDO));
	}

	/**
	 * Force the connection to close.
	 *
	 * @return void
	 */
	public function closeConnection()
	{
		$this -> _connection = null;
	}

	/**
	 * Prepares an SQL statement.
	 *
	 * @param string $sql The SQL statement with placeholders.
	 * @param array $bind An array of data to bind to the placeholders.
	 * @return PDOStatement
	 */
	public function prepare($sql)
	{
		$this -> _connect();
		$stmtClass = $this -> _defaultStmtClass;
		
		$stmt = new $stmtClass($this, $sql);
		$stmt -> setFetchMode($this -> _fetchMode);
		return $stmt;
	}

	/**
	 * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
	 *
	 * As a convention, on RDBMS brands that support sequences
	 * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
	 * from the arguments and returns the last id generated by that sequence.
	 * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
	 * returns the last value generated for such a column, and the table name
	 * argument is disregarded.
	 *
	 * On RDBMS brands that don't support sequences, $tableName and $primaryKey
	 * are ignored.
	 *
	 * @param string $tableName   OPTIONAL Name of table.
	 * @param string $primaryKey  OPTIONAL Name of primary key column.
	 * @return string
	 */
	public function lastInsertId($tableName = null, $primaryKey = null)
	{
		$this -> _connect();
		return $this -> _connection -> lastInsertId();
	}

	/**
	 * Special handling for PDO query().
	 * All bind parameter names must begin with ':'
	 *
	 * @param string|Database_Select $sql The SQL statement with placeholders.
	 * @param array $bind An array of data to bind to the placeholders.
	 * @return Database_Statement_Pdo
	 * @throws Database_Exception To re-throw PDOException.
	 */
	public function query($sql, $bind = array())
	{
		if (empty($bind) && $sql instanceof Database_Select)
		{
			$bind = $sql -> getBind();
		}

		if (is_array($bind))
		{
			foreach ($bind as $name => $value)
			{
				if (!is_int($name) && !preg_match('/^:/', $name))
				{
					$newName = ":$name";
					unset($bind[$name]);
					$bind[$newName] = $value;
				}
			}
		}

		try
		{
			return parent::query($sql, $bind);
		}
		catch (PDOException $e)
		{
			/**
			 * @see Database_Exception
			 */
			
			throw new Database_Exception($e -> getMessage(), $e -> getCode(), $e);
		}
	}

	/**
	 * Executes an SQL statement and return the number of affected rows
	 *
	 * @param  mixed  $sql  The SQL statement with placeholders.
	 *                      May be a string or Database_Select.
	 * @return integer      Number of rows that were modified
	 *                      or deleted by the SQL statement
	 */
	public function exec($sql)
	{
		if ($sql instanceof Database_Select)
		{
			$sql = $sql -> assemble();
		}

		try
		{
			$affected = $this -> getConnection() -> exec($sql);

			if ($affected === false)
			{
				$errorInfo = $this -> getConnection() -> errorInfo();
				/**
				 * @see Database_Exception
				 */

				throw new Database_Exception($errorInfo[2]);
			}

			return $affected;
		}
		catch (PDOException $e)
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception($e -> getMessage(), $e -> getCode(), $e);
		}
	}

	/**
	 * Quote a raw string.
	 *
	 * @param string $value     Raw string
	 * @return string           Quoted string
	 */
	protected function _quote($value)
	{
		if (is_int($value) || is_float($value))
		{
			return $value;
		}
		$this -> _connect();
		return $this -> _connection -> quote($value);
	}

	/**
	 * Begin a transaction.
	 */
	protected function _beginTransaction()
	{
		$this -> _connect();
		$this -> _connection -> beginTransaction();
	}

	/**
	 * Commit a transaction.
	 */
	protected function _commit()
	{
		$this -> _connect();
		$this -> _connection -> commit();
	}

	/**
	 * Roll-back a transaction.
	 */
	protected function _rollBack()
	{
		$this -> _connect();
		$this -> _connection -> rollBack();
	}

	/**
	 * Set the PDO fetch mode.
	 *
	 * @todo Support FETCH_CLASS and FETCH_INTO.
	 *
	 * @param int $mode A PDO fetch mode.
	 * @return void
	 * @throws Database_Exception
	 */
	public function setFetchMode($mode)
	{
		//check for PDO extension
		if (!extension_loaded('pdo'))
		{
			/**
			 * @see Database_Exception
			 */

			throw new Database_Exception('The PDO extension is required for this adapter but the extension is not loaded');
		}
		switch ($mode)
		{
			case PDO::FETCH_LAZY :
			case PDO::FETCH_ASSOC :
			case PDO::FETCH_NUM :
			case PDO::FETCH_BOTH :
			case PDO::FETCH_NAMED :
			case PDO::FETCH_OBJ :
				$this -> _fetchMode = $mode;
				break;
			default :
				/**
				 * @see Database_Exception
				 */

				throw new Database_Exception("Invalid fetch mode '$mode' specified");
				break;
		}
	}

	/**
	 * Check if the adapter supports real SQL parameters.
	 *
	 * @param string $type 'positional' or 'named'
	 * @return bool
	 */
	public function supportsParameters($type)
	{
		switch ($type)
		{
			case 'positional' :
			case 'named' :
			default :
				return true;
		}
	}

	/**
	 * Retrieve server version in PHP style
	 *
	 * @return string
	 */
	public function getServerVersion()
	{
		$this -> _connect();
		try
		{
			$version = $this -> _connection -> getAttribute(PDO::ATTR_SERVER_VERSION);
		}
		catch (PDOException $e)
		{
			// In case of the driver doesn't support getting attributes
			return null;
		}
		$matches = null;
		if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches))
		{
			return $matches[1];
		}
		else
		{
			return null;
		}
	}

    /**
     * Returns a list of the tables in the database.
     *
     * @return array
     */
    public function listTables()
    {
        // @todo use a better query with joins instead of subqueries
        $sql = "SELECT c.relname AS table_name "
             . "FROM pg_class c, pg_user u "
             . "WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
             . "AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
             . "AND c.relname !~ '^(pg_|sql_)' "
             . "UNION "
             . "SELECT c.relname AS table_name "
             . "FROM pg_class c "
             . "WHERE c.relkind = 'r' "
             . "AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
             . "AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) "
             . "AND c.relname !~ '^pg_'";

        return $this->fetchCol($sql);
    }

    /**
     * Returns the column descriptions for a table.
     *
     * The return value is an associative array keyed by the column name,
     * as returned by the RDBMS.
     *
     * The value of each array element is an associative array
     * with the following keys:
     *
     * SCHEMA_NAME      => string; name of database or schema
     * TABLE_NAME       => string;
     * COLUMN_NAME      => string; column name
     * COLUMN_POSITION  => number; ordinal position of column in table
     * DATA_TYPE        => string; SQL datatype name of column
     * DEFAULT          => string; default expression of column, null if none
     * NULLABLE         => boolean; true if column can have nulls
     * LENGTH           => number; length of CHAR/VARCHAR
     * SCALE            => number; scale of NUMERIC/DECIMAL
     * PRECISION        => number; precision of NUMERIC/DECIMAL
     * UNSIGNED         => boolean; unsigned property of an integer type
     * PRIMARY          => boolean; true if column is part of the primary key
     * PRIMARY_POSITION => integer; position of column in primary key
     * IDENTITY         => integer; true if column is auto-generated with unique values
     *
     * @todo Discover integer unsigned property.
     *
     * @param  string $tableName
     * @param  string $schemaName OPTIONAL
     * @return array
     */
    public function describeTable($tableName, $schemaName = null)
    {
        $sql = "SELECT
                a.attnum,
                n.nspname,
                c.relname,
                a.attname AS colname,
                t.typname AS type,
                a.atttypmod,
                FORMAT_TYPE(a.atttypid, a.atttypmod) AS complete_type,
                d.adsrc AS default_value,
                a.attnotnull AS notnull,
                a.attlen AS length,
                co.contype,
                ARRAY_TO_STRING(co.conkey, ',') AS conkey
            FROM pg_attribute AS a
                JOIN pg_class AS c ON a.attrelid = c.oid
                JOIN pg_namespace AS n ON c.relnamespace = n.oid
                JOIN pg_type AS t ON a.atttypid = t.oid
                LEFT OUTER JOIN pg_constraint AS co ON (co.conrelid = c.oid
                    AND a.attnum = ANY(co.conkey) AND co.contype = 'p')
                LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
            WHERE a.attnum > 0 AND c.relname = ".$this->quote($tableName);
        if ($schemaName) {
            $sql .= " AND n.nspname = ".$this->quote($schemaName);
        }
        $sql .= ' ORDER BY a.attnum';

        $stmt = $this->query($sql);

        // Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
        $result = $stmt->fetchAll(Database::FETCH_NUM);

        $attnum        = 0;
        $nspname       = 1;
        $relname       = 2;
        $colname       = 3;
        $type          = 4;
        $atttypemod    = 5;
        $complete_type = 6;
        $default_value = 7;
        $notnull       = 8;
        $length        = 9;
        $contype       = 10;
        $conkey        = 11;

        $desc = array();
        foreach ($result as $key => $row) {
            $defaultValue = $row[$default_value];
            if ($row[$type] == 'varchar' || $row[$type] == 'bpchar' ) {
                if (preg_match('/character(?: varying)?(?:\((\d+)\))?/', $row[$complete_type], $matches)) {
                    if (isset($matches[1])) {
                        $row[$length] = $matches[1];
                    } else {
                        $row[$length] = null; // unlimited
                    }
                }
                if (preg_match("/^'(.*?)'::(?:character varying|bpchar)$/", $defaultValue, $matches)) {
                    $defaultValue = $matches[1];
                }
            }
            list($primary, $primaryPosition, $identity) = array(false, null, false);
            if ($row[$contype] == 'p') {
                $primary = true;
                $primaryPosition = array_search($row[$attnum], explode(',', $row[$conkey])) + 1;
                $identity = (bool) (preg_match('/^nextval/', $row[$default_value]));
            }
            $desc[$row[$colname]] = array(
                'SCHEMA_NAME'      => $row[$nspname],
                'TABLE_NAME'       => $row[$relname],
                'COLUMN_NAME'      => $row[$colname],
                'COLUMN_POSITION'  => $row[$attnum],
                'DATA_TYPE'        => $row[$type],
                'DEFAULT'          => $defaultValue,
                'NULLABLE'         => (bool) ($row[$notnull] != 't'),
                'LENGTH'           => $row[$length],
                'SCALE'            => null, // @todo
                'PRECISION'        => null, // @todo
                'UNSIGNED'         => null, // @todo
                'PRIMARY'          => $primary,
                'PRIMARY_POSITION' => $primaryPosition,
                'IDENTITY'         => $identity
            );
        }
        return $desc;
    }


    /**
     * Adds an adapter-specific LIMIT clause to the SELECT statement.
     *
     * @param string $sql
     * @param integer $count
     * @param integer $offset OPTIONAL
     * @return string
     */
    public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0) {
            /**
             * @see Database_Exception
             */
            
            throw new Database_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /**
             * @see Database_Exception
             */
            
            throw new Database_Exception("LIMIT argument offset=$offset is not valid");
        }

        $sql .= " LIMIT $count";
        if ($offset > 0) {
            $sql .= " OFFSET $offset";
        }

        return $sql;
    }

    /**
     * Return the most recent value from the specified sequence in the database.
     * This is supported only on RDBMS brands that support sequences
     * (e.g. Oracle, PostgreSQL, DB2).  Other RDBMS brands return null.
     *
     * @param string $sequenceName
     * @return string
     */
    public function lastSequenceId($sequenceName)
    {
        $this->_connect();
        $value = $this->fetchOne("SELECT CURRVAL(".$this->quote($sequenceName).")");
        return $value;
    }

    /**
     * Generate a new value from the specified sequence in the database, and return it.
     * This is supported only on RDBMS brands that support sequences
     * (e.g. Oracle, PostgreSQL, DB2).  Other RDBMS brands return null.
     *
     * @param string $sequenceName
     * @return string
     */
    public function nextSequenceId($sequenceName)
    {
        $this->_connect();
        $value = $this->fetchOne("SELECT NEXTVAL(".$this->quote($sequenceName).")");
        return $value;
    }

    /**
     * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
     *
     * As a convention, on RDBMS brands that support sequences
     * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
     * from the arguments and returns the last id generated by that sequence.
     * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
     * returns the last value generated for such a column, and the table name
     * argument is disregarded.
     *
     * @param string $tableName   OPTIONAL Name of table.
     * @param string $primaryKey  OPTIONAL Name of primary key column.
     * @return string
     */
    public function lastInsertId($tableName = null, $primaryKey = null)
    {
        if ($tableName !== null) {
            $sequenceName = $tableName;
            if ($primaryKey) {
                $sequenceName .= "_$primaryKey";
            }
            $sequenceName .= '_seq';
            return $this->lastSequenceId($sequenceName);
        }
        return $this->_connection->lastInsertId($tableName);
    }

}
